package DBLayer;
import ModelLayer.Product;
import ModelLayer.Sale;
import java.sql.*;
import java.util.ArrayList;
import ModelLayer.SalesLine;
import ControlLayer.CtrProduct;

public class DBSalesLine implements IFDBSalesLine
{
    private Connection con;
    
    public DBSalesLine() 
    {
        con = DBConnection.getInstance().getDBcon();
    }
    
    private ArrayList<SalesLine> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<SalesLine> list = new ArrayList<SalesLine>();

        String query =  buildQuery(wClause);
        System.out.println(query);
        try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            int snr = 0;

            while(results.next())
            {
                 SalesLine salesLineObj = new SalesLine();
                 salesLineObj = buildSalesLine(results);	
                 list.add(salesLineObj);
            }

            stmt.close();
            if(retrieveAssociation){
                for(SalesLine s : list){
                    IFDBSale dbSaleObj = new DBSale();
                    Sale saleObj = dbSaleObj.findSale(s.getSale().getSaleID(), false);
                    System.out.println("Sale order id is selected");
                    s.setSale(saleObj);
                    
                    IFDBProduct dbProdObj = new DBProduct();
                    Product prodObj = dbProdObj.findProduct(s.getProductID(), false);
                    System.out.println("Product is selected");
                    s.setProductID(prodObj);
                }
                
            }
        }
        catch(Exception e)
        {

        }

        return list;
    }
    
    private SalesLine buildSalesLine(ResultSet results)
    {
        SalesLine salesLineObj = new SalesLine();
        
        try
        {
           salesLineObj.setSalesLineID(results.getInt(1));
           salesLineObj.setSale(new Sale(results.getInt(2))); 
           salesLineObj.setProduct(new Product(results.getInt(3)));
           salesLineObj.setQuantity(results.getInt(4));
        }
        catch(Exception e) {}
        
        return salesLineObj;
    }
 
    private String buildQuery(String wClause)
    {
        String query="SELECT * FROM SaleLine";

        if (wClause.length() > 0)
        {
            query = query + " WHERE " + wClause;
        }
        
        return query;
    }

    private void insertSalesLine(SalesLine salesLineObj)
    {  
        String query = "INSERT INTO SaleLine(pId, sId, amount) VALUES('" 
                + salesLineObj.getProduct().getProductID() + "', " +
                "((SELECT  dt.number FROM (SELECT max(sId) AS number FROM Sale) AS dt))," +
                "'" + salesLineObj.getQuantity() + "')";
                    
        System.out.println("Update query: " + query);
        try
        { 
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);

            stmt.close();
        }
        catch(Exception ex)
        {
            
        }
    }
    
    @Override
    public ArrayList<SalesLine> listSalesLines(int saleID)
    {
        return miscWhere("sId = " + saleID, true);
    }
    
    @Override
    public void insertSalesLines(ArrayList<SalesLine> listSalesLine)
    {
        CtrProduct ctrProduct = new CtrProduct();
        
        for (int i = 0; i < listSalesLine.size(); i++) 
        {
            SalesLine salesLine = listSalesLine.get(i);
            Product product = ctrProduct.findByID(salesLine.getProduct().getProductID());
            
            int newStock = product.getCurrentStock() - salesLine.getQuantity();
            
            ctrProduct.updateProductStock(salesLine.getProductID(), newStock);
           
            insertSalesLine(salesLine);

            System.out.println(product.getCurrentStock());
        }
        
    }
    
    @Override
    public void deleteSalesLines(int saleID)
    {
        String query = "DELETE FROM SalesLine WHERE sId = '" +
                        saleID + "'";

        System.out.println(query);

        try
        { 
                Statement stmt = con.createStatement();
                stmt.setQueryTimeout(5);
                stmt.executeUpdate(query);
                stmt.close();
        }
        catch(Exception ex)
        {

        }
    }
}
